"""
cron: 0 */4 * * *
new Env('旅游景点分析专业版');
"""
import requests
import os
import mysql.connector
import json
import time
from datetime import datetime
from bs4 import BeautifulSoup

class TravelAnalysisPro:
    def __init__(self):
        self.connection = mysql.connector.connect(
            host=os.environ.get('HOST'),
            user=os.environ.get('USER'),
            password=os.environ.get('PASSWORD'),
            database=os.environ.get('DATABASE')
        )
        self.cursor = self.connection.cursor()
        self.create_tables()
        
    def create_tables(self):
        """创建必要的数据表"""
        # 景点信息表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_scenic_info (
                id INT AUTO_INCREMENT PRIMARY KEY,
                scenic_id VARCHAR(50),
                name VARCHAR(200),
                city VARCHAR(50),
                level VARCHAR(20),
                price DECIMAL(10,2),
                score DECIMAL(3,1),
                visit_count INT,
                comment_count INT,
                features TEXT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_city (city),
                INDEX idx_level (level),
                INDEX idx_create_time (create_time)
            )
        """)
        
        # 游客评价表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_scenic_comments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                scenic_id VARCHAR(50),
                user_id VARCHAR(50),
                score DECIMAL(2,1),
                comment TEXT,
                visit_time DATE,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_scenic_id (scenic_id),
                INDEX idx_score (score)
            )
        """)
        
        # 景点分析表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_scenic_analysis (
                id INT AUTO_INCREMENT PRIMARY KEY,
                scenic_id VARCHAR(50),
                avg_score DECIMAL(3,1),
                price_level INT,
                popularity_level INT,
                peak_season VARCHAR(20),
                best_time VARCHAR(100),
                crowd_index DECIMAL(5,2),
                sentiment_score DECIMAL(5,2),
                recommendation_index INT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_scenic_id (scenic_id),
                INDEX idx_recommendation_index (recommendation_index)
            )
        """)

    def get_mafengwo_data(self, city):
        """获取马蜂窝景点数据"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
            }
            
            url = f'https://www.mafengwo.cn/jd/{city}/gonglve.html'
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                soup = BeautifulSoup(response.text, 'html.parser')
                scenic_list = soup.find_all('div', class_='scenic-item')
                
                for scenic in scenic_list:
                    # 提取景点信息
                    name = scenic.find('h3').text.strip()
                    scenic_id = scenic.find('a')['data-id']
                    level = scenic.find('span', class_='level').text if scenic.find('span', class_='level') else ''
                    score = float(scenic.find('span', class_='score').text) if scenic.find('span', class_='score') else 0
                    
                    # 获取详细信息
                    detail_url = f'https://www.mafengwo.cn/poi/{scenic_id}.html'
                    detail_response = requests.get(detail_url, headers=headers)
                    detail_soup = BeautifulSoup(detail_response.text, 'html.parser')
                    
                    price = float(detail_soup.find('span', class_='price').text.strip('¥')) if detail_soup.find('span', class_='price') else 0
                    visit_count = int(detail_soup.find('span', class_='visit-count').text) if detail_soup.find('span', class_='visit-count') else 0
                    comment_count = int(detail_soup.find('span', class_='comment-count').text) if detail_soup.find('span', class_='comment-count') else 0
                    
                    features = [tag.text for tag in detail_soup.find_all('span', class_='feature-tag')]
                    
                    scenic_data = {
                        'scenic_id': scenic_id,
                        'name': name,
                        'city': city,
                        'level': level,
                        'price': price,
                        'score': score,
                        'visit_count': visit_count,
                        'comment_count': comment_count,
                        'features': json.dumps(features, ensure_ascii=False)
                    }
                    
                    self.save_scenic_data(scenic_data)
                    
                    # 获取评论数据
                    self.get_comments(scenic_id)
                    time.sleep(2)  # 避免请求过快
                    
        except Exception as e:
            print(f"获取马蜂窝数据失败: {str(e)}")

    def get_comments(self, scenic_id):
        """获取景点评论数据"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
            }
            
            url = f'https://www.mafengwo.cn/poi/comment_{scenic_id}.html'
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                soup = BeautifulSoup(response.text, 'html.parser')
                comments = soup.find_all('div', class_='comment-item')
                
                for comment in comments:
                    user_id = comment.find('a', class_='user-name')['href'].split('/')[-1]
                    score = float(comment.find('span', class_='score').text) if comment.find('span', class_='score') else 0
                    content = comment.find('p', class_='comment-content').text.strip()
                    visit_time = datetime.strptime(comment.find('span', class_='visit-time').text, '%Y-%m-%d')
                    
                    comment_data = {
                        'scenic_id': scenic_id,
                        'user_id': user_id,
                        'score': score,
                        'comment': content,
                        'visit_time': visit_time
                    }
                    
                    self.save_comment_data(comment_data)
                    
        except Exception as e:
            print(f"获取评论数据失败: {str(e)}")

    def analyze_scenic(self, scenic_id):
        """分析景点数据"""
        try:
            # 获取基础数据
            query = """
                SELECT price, score, visit_count, comment_count
                FROM ningm_scenic_info
                WHERE scenic_id = %s
            """
            self.cursor.execute(query, (scenic_id,))
            result = self.cursor.fetchone()
            
            if not result:
                return
                
            price, score, visit_count, comment_count = result
            
            # 计算价格水平（1-5）
            price_level = self.calculate_price_level(price)
            
            # 计算人气水平（1-5）
            popularity_level = self.calculate_popularity(visit_count, comment_count)
            
            # 分析旺季和最佳游玩时间
            peak_season, best_time = self.analyze_best_time(scenic_id)
            
            # 计算拥挤指数
            crowd_index = self.calculate_crowd_index(scenic_id)
            
            # 情感分析得分
            sentiment_score = self.analyze_sentiment(scenic_id)
            
            # 综合推荐指数（1-5星）
            recommendation_index = self.calculate_recommendation(
                score,
                price_level,
                popularity_level,
                crowd_index,
                sentiment_score
            )
            
            # 保存分析结果
            insert_query = """
                INSERT INTO ningm_scenic_analysis 
                (scenic_id, avg_score, price_level, popularity_level, 
                 peak_season, best_time, crowd_index, sentiment_score, 
                 recommendation_index) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                scenic_id,
                score,
                price_level,
                popularity_level,
                peak_season,
                best_time,
                crowd_index,
                sentiment_score,
                recommendation_index
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"分析景点数据失败: {str(e)}")
            self.connection.rollback()

    def calculate_price_level(self, price):
        """计算价格水平"""
        if price <= 50:
            return 5
        elif price <= 100:
            return 4
        elif price <= 200:
            return 3
        elif price <= 300:
            return 2
        else:
            return 1

    def calculate_popularity(self, visit_count, comment_count):
        """计算人气水平"""
        score = 0
        
        # 访问量评分
        if visit_count > 10000:
            score += 2.5
        elif visit_count > 5000:
            score += 2
        elif visit_count > 1000:
            score += 1.5
        elif visit_count > 500:
            score += 1
        
        # 评论数评分
        if comment_count > 1000:
            score += 2.5
        elif comment_count > 500:
            score += 2
        elif comment_count > 100:
            score += 1.5
        elif comment_count > 50:
            score += 1
            
        return min(int(score), 5)

    def analyze_best_time(self, scenic_id):
        """分析最佳游玩时间"""
        try:
            query = """
                SELECT MONTH(visit_time) as month, COUNT(*) as count,
                       AVG(score) as avg_score
                FROM ningm_scenic_comments
                WHERE scenic_id = %s
                GROUP BY MONTH(visit_time)
                ORDER BY count DESC, avg_score DESC
            """
            self.cursor.execute(query, (scenic_id,))
            results = self.cursor.fetchall()
            
            if not results:
                return "未知", "未知"
                
            # 找出访问量最高的月份作为旺季
            peak_month = results[0][0]
            peak_season = self.get_season(peak_month)
            
            # 找出评分最高的月份作为最佳游玩时间
            best_months = sorted(results, key=lambda x: x[2], reverse=True)[:3]
            best_time = "建议在" + ",".join([f"{m[0]}月" for m in best_months]) + "游玩"
            
            return peak_season, best_time
            
        except Exception as e:
            print(f"分析最佳游玩时间失败: {str(e)}")
            return "未知", "未知"

    def get_season(self, month):
        """根据月份获取季节"""
        if month in [3, 4, 5]:
            return "春季"
        elif month in [6, 7, 8]:
            return "夏季"
        elif month in [9, 10, 11]:
            return "秋季"
        else:
            return "冬季"

    def calculate_crowd_index(self, scenic_id):
        """计算拥挤指数"""
        try:
            query = """
                SELECT COUNT(*) as visit_count,
                       (SELECT COUNT(*) FROM ningm_scenic_comments 
                        WHERE scenic_id = %s AND 
                        comment LIKE '%%拥挤%%' OR comment LIKE '%%人多%%') as crowd_mentions
                FROM ningm_scenic_comments
                WHERE scenic_id = %s
            """
            self.cursor.execute(query, (scenic_id, scenic_id))
            result = self.cursor.fetchone()
            
            if not result or result[0] == 0:
                return 0
                
            total_comments, crowd_mentions = result
            return (crowd_mentions / total_comments) * 100
            
        except Exception as e:
            print(f"计算拥挤指数失败: {str(e)}")
            return 0

    def analyze_sentiment(self, scenic_id):
        """简单的情感分析"""
        try:
            # 获取最近的评论
            query = """
                SELECT comment
                FROM ningm_scenic_comments
                WHERE scenic_id = %s
                ORDER BY create_time DESC
                LIMIT 100
            """
            self.cursor.execute(query, (scenic_id,))
            comments = self.cursor.fetchall()
            
            if not comments:
                return 0
                
            # 简单的情感词典
            positive_words = ['好', '棒', '赞', '美', '喜欢', '推荐', '值得', '不错']
            negative_words = ['差', '烂', '贵', '失望', '无聊', '不值', '坑']
            
            total_score = 0
            for comment in comments:
                score = 0
                text = comment[0]
                
                # 计算正面词出现次数
                for word in positive_words:
                    score += text.count(word)
                    
                # 计算负面词出现次数
                for word in negative_words:
                    score -= text.count(word)
                    
                total_score += score
                
            # 归一化到0-100
            avg_score = (total_score / len(comments) + 3) * 20  # +3使得分数集中在60分附近
            return max(0, min(100, avg_score))
            
        except Exception as e:
            print(f"情感分析失败: {str(e)}")
            return 0

    def calculate_recommendation(self, score, price_level, popularity_level, crowd_index, sentiment_score):
        """计算综合推荐指数"""
        try:
            # 权重设置
            weights = {
                'score': 0.3,
                'price': 0.2,
                'popularity': 0.2,
                'crowd': 0.1,
                'sentiment': 0.2
            }
            
            # 评分转换为5分制
            score = score / 2
            
            # 拥挤指数转换为5分制（越拥挤分数越低）
            crowd_score = 5 - (crowd_index / 20)
            
            # 情感得分转换为5分制
            sentiment_score = sentiment_score / 20
            
            # 计算加权得分
            final_score = (
                score * weights['score'] +
                price_level * weights['price'] +
                popularity_level * weights['popularity'] +
                crowd_score * weights['crowd'] +
                sentiment_score * weights['sentiment']
            )
            
            return min(int(round(final_score)), 5)
            
        except Exception as e:
            print(f"计算推荐指数失败: {str(e)}")
            return 0

    def save_scenic_data(self, scenic_data):
        """保存景点数据"""
        try:
            insert_query = """
                INSERT INTO ningm_scenic_info 
                (scenic_id, name, city, level, price, score, 
                 visit_count, comment_count, features) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                scenic_data['scenic_id'],
                scenic_data['name'],
                scenic_data['city'],
                scenic_data['level'],
                scenic_data['price'],
                scenic_data['score'],
                scenic_data['visit_count'],
                scenic_data['comment_count'],
                scenic_data['features']
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"保存景点数据失败: {str(e)}")
            self.connection.rollback()

    def save_comment_data(self, comment_data):
        """保存评论数据"""
        try:
            insert_query = """
                INSERT INTO ningm_scenic_comments 
                (scenic_id, user_id, score, comment, visit_time) 
                VALUES (%s, %s, %s, %s, %s)
            """
            insert_values = (
                comment_data['scenic_id'],
                comment_data['user_id'],
                comment_data['score'],
                comment_data['comment'],
                comment_data['visit_time']
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"保存评论数据失败: {str(e)}")
            self.connection.rollback()

    def run(self):
        """运行分析系统"""
        try:
            # 热门城市列表
            cities = ['beijing', 'shanghai', 'guangzhou', 'shenzhen', 
                     'hangzhou', 'chengdu', 'xian', 'sanya']
            
            for city in cities:
                # 1. 获取景点数据
                self.get_mafengwo_data(city)
                
                # 2. 分析每个景点
                self.cursor.execute("SELECT scenic_id FROM ningm_scenic_info WHERE city = %s", (city,))
                scenic_ids = self.cursor.fetchall()
                
                for scenic_id in scenic_ids:
                    self.analyze_scenic(scenic_id[0])
                    time.sleep(1)  # 避免请求过快
                
                time.sleep(5)  # 城市之间添加间隔
                
        except Exception as e:
            print(f"系统运行失败: {str(e)}")
        finally:
            self.cursor.close()
            self.connection.close()

if __name__ == "__main__":
    analyzer = TravelAnalysisPro()
    analyzer.run() 